package com.qianfeng.jdbc2;


//
//                            _ooOoo_  
//                           o8888888o  
//                           88" . "88  
//                           (| -_- |)  
//                            O\ = /O  
//                        ____/`---'\____  
//                      .   ' \\| |// `.  
//                       / \\||| : |||// \  
//                     / _||||| -:- |||||- \  
//                       | | \\\ - /// | |  
//                     | \_| ''\---/'' | |  
//                      \ .-\__ `-` ___/-. /  
//                   ___`. .' /--.--\ `. . __  
//                ."" '< `.___\_<|>_/___.' >'"".  
//               | | : `- \`.;`\ _ /`;.`/ - ` : | |  
//                 \ \ `-. \_ __\ /__ _/ .-` / /  
//         ======`-.____`-.___\_____/___.-`____.-'======  
//                            `=---='  
//  
//         .............................................  
//                  佛祖镇楼            BUG辟易  
//          佛曰:  
//                  写字楼里写字间，写字间里程序员；  
//                  程序人员写程序，又拿程序换酒钱。  
//                  酒醒只在网上坐，酒醉还来网下眠；  
//                  酒醉酒醒日复日，网上网下年复年。  
//                  但愿老死电脑间，不愿鞠躬老板前；  
//                  奔驰宝马贵者趣，公交自行程序员。  
//                  别人笑我忒疯癫，我笑自己命太贱；  


import com.qianfeng.pojo.Student;
import com.qianfeng.utils.DBUtils;
import com.qianfeng.utils.DateUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by jackiechan on 2021/8/22 10:27
 * 封装有数据库访问操作的对象
 * @author jackiechan
 * @version 1.0
 * @since 1.0
 */

public class StudentDaoImpl {
    /**
     * 将参数数据保存到数据库中
     * @param student
     * @return
     */
    public int addStudent(Student student) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "insert into student (stuName,stuGender,stuPhone,bornDate,stuEmail,stuAddress,gradeId) values ('"+student.getStuName()+"','"+student.getStuGender()+"','"+student.getStuPhone()+"','"+ DateUtils.getDateString(student.getBornDate()) +"','"+student.getStuEmail()+"','"+student.getStuAddress()+"','"+student.getGradeId()+"')";
        System.err.println(sql);
        int result = statement.executeUpdate(sql);
        DBUtils.closeResources(connection, statement, null);
        return result;
    }


    public int deleteStudent(int id) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate("delete from student where stuId='" + id + "'");
        DBUtils.closeResources(connection, statement, null);
        return result;
    }


    public int updateStudent(Student student) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "update student set stuName='" + student.getStuName() + "'," + "stuGender='" + student.getStuGender() + "' where stuId ='" + student.getStuId() + "'";
        System.err.println(sql);
        int result = statement.executeUpdate(sql);
        DBUtils.closeResources(connection, statement, null);
        return result;
    }


    public Student getStudentById(int id) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select  * from student where stuId ='" + id + "'");
        Student student = null;
        if (resultSet.next()) {
            //这里用 if 的原因是因为主键查询最多就返回一条数据
            student = new Student();//如果有结果就创建对象,否则直接返回的就是 null 这样我们可以根据 null 来判断没有查询结果
            student.setStuId(resultSet.getInt("stuId"));
            student.setStuName(resultSet.getString("stuName"));
            student.setStuAddress(resultSet.getString("stuAddress"));
            student.setStuGender(resultSet.getString("stuGender"));
            student.setStuPhone(resultSet.getString("stuPhone"));
            student.setGradeId(resultSet.getInt("gradeId"));
            student.setBornDate(resultSet.getDate("bornDate"));
            student.setStuEmail(resultSet.getString("stuEmail"));
        }
        DBUtils.closeResources(connection, statement, null);
        return student;
    }

    //如果按照名字查询,会返回多少数据? 没有,一个,多个
   public List<Student> getStudenByName(String name) throws SQLException {
       Connection connection = DBUtils.getConnection();
       Statement statement = connection.createStatement();
       List<Student> studentList = new ArrayList<>();
       //这个查询可能会返回多个数据
       ResultSet resultSet = statement.executeQuery("select * from student where stuName = '" + name + "'");
       while (resultSet.next()) {
           //这次用 while 循环的愿意是因为可能会有多个数据
           Student student = new Student();//创建学生对象,结束当前行的数据
           student.setStuId(resultSet.getInt("stuId"));
           student.setStuName(resultSet.getString("stuName"));
           student.setStuAddress(resultSet.getString("stuAddress"));
           student.setStuGender(resultSet.getString("stuGender"));
           student.setStuPhone(resultSet.getString("stuPhone"));
           student.setGradeId(resultSet.getInt("gradeId"));
           student.setBornDate(resultSet.getDate("bornDate"));
           student.setStuEmail(resultSet.getString("stuEmail"));
           studentList.add(student);//将结果保存到集合中
       }
       DBUtils.closeResources(connection,statement, null);
       return studentList;//返回集合
   }


    public List<Student> getStudentsByLimit(int page, int size) throws SQLException {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        int from = (page - 1) * size;//偏移量
        String sql = "select * from student limit " + from + "," + size + "";
        System.err.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        List<Student> studentList = new ArrayList<>();//保存查询到的所有的数据的集合
        while (resultSet.next()) {
            //这次用 while 循环的愿意是因为可能会有多个数据
            Student student = new Student();//创建学生对象,结束当前行的数据
            student.setStuId(resultSet.getInt("stuId"));
            student.setStuName(resultSet.getString("stuName"));
            student.setStuAddress(resultSet.getString("stuAddress"));
            student.setStuGender(resultSet.getString("stuGender"));
            student.setStuPhone(resultSet.getString("stuPhone"));
            student.setGradeId(resultSet.getInt("gradeId"));
            student.setBornDate(resultSet.getDate("bornDate"));
            student.setStuEmail(resultSet.getString("stuEmail"));
            studentList.add(student);//将结果保存到集合中
        }

        DBUtils.closeResources(connection, statement, null);
        return studentList;
    }
}
